package sz.com.postoffice.jxyzwgcase.service.impl;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;
import sz.com.postoffice.jxyzwgcase.dao.node.IOneClickResponseDao;
import sz.com.postoffice.jxyzwgcase.pojo.CollectionMarketingListModel;
import sz.com.postoffice.jxyzwgcase.pojo.ConstantDb;
import sz.com.postoffice.jxyzwgcase.service.IOneClickResponseService;
import sz.com.postoffice.jxyzwgcase.service.ConstantDbService;


@Service
public class OneClickResponseService implements IOneClickResponseService {

    private static Logger logger = LoggerFactory.getLogger(OneClickResponseService.class);

    @Autowired
    private IOneClickResponseDao oneClickResponseDao;

    @Autowired
    private ConstantDbService constantDbService;

    @Override
    public List<Map> collectionMarketingForCity(Map map) {
        Map paramMap = formatTime(map);
        List<String> fields = getFields(paramMap);
        paramMap.put("fields", fields);
        List<Map> resultMap = oneClickResponseDao.collectionMarketingForCity(paramMap);
        setOnTimeRate(resultMap);
        return resultMap;
    }

    @Override
    public List<Map> collectionMarketingForCounty(Map map) {
        Map paramMap = formatTime(map);
        List<String> fields = getFields(paramMap);
        paramMap.put("fields", fields);
        List<Map> resultMap = oneClickResponseDao.collectionMarketingForCounty(paramMap);
        setOnTimeRate(resultMap);
        return resultMap;
    }

    @Override
    public List<Map> collectionMarketingForDepartment(Map map) {
        Map paramMap = formatTime(map);
        List<String> fields = getFields(paramMap);
        paramMap.put("fields", fields);
        List<Map> resultMap = oneClickResponseDao.collectionMarketingForDepartment(paramMap);
        setOnTimeRate(resultMap);
        return resultMap;
    }

    @Override
    public Map collectionMarketingList(Map map) {
    	Map mapList = getResultMap(map, null, null);
        List<CollectionMarketingListModel> resultList = oneClickResponseDao.collectionMarketingList(mapList);
        if (resultList.size() < 1) {
            return getResultMap(map, resultList, 0);
        }
        //处理结束时间
        for (CollectionMarketingListModel collectionMarketingListModel : resultList) {
            String status = collectionMarketingListModel.getStatus();
            if (!status.equals("Completed")) {
                collectionMarketingListModel.setLast_updated_date(null);
            }
        }
        int count = oneClickResponseDao.collectionMarketingListCount(mapList);
        return getResultMap(map, resultList, count);
    }


    /**
     * 此方法暂不使用
     *
     * @param map
     * @return
     */
    @Deprecated
    @Override
    public Map directMailSearchList(Map map) {
        int pageSize = (int) map.get("pageSize");
        int curPage = (int) map.get("curPage");
        Map paramMap = formatTime(map);
        int startIndex = (curPage - 1) * pageSize;
        map.put("startIndex", startIndex);
        List<String> fields = getFields(map);
        map.put("fields", fields);
        List<Map> resultList = oneClickResponseDao.directMailSearchList(map);
        logger.info("--数据库查询结果参数---：" + map);
        //过滤返回参数
        for (Map resMap : resultList) {
            String status = resMap.get("status").toString();
            if (!status.equals("Completed")) {
                resMap.put("last_updated_date", "");
            }
        }
        //formatDate(resultList);接口未使用，如果后期使用将接口的返回参数设置为data
        Map resultMap = new HashMap();
        Map pagerMap = new HashMap();
        int count = oneClickResponseDao.directMailSearchListCount(map);
        pagerMap.put("totalCount", count);
        pagerMap.put("pageSize", pageSize);
        pagerMap.put("curPage", curPage);
        resultMap.put("pagerVO", pagerMap);
        resultMap.put("result", resultList);
        return resultMap;
    }

    /**
     * 对入参或者出参进行处理
     *
     * @param map
     * @param resultList
     * @param count
     * @return
     */
    private Map getResultMap(Map map, List<CollectionMarketingListModel> resultList, Integer count) {
        int pageSize = (int) map.get("pageSize");
        int curPage = (int) map.get("curPage");
        if (null == resultList) {
            Map paramMap = formatTime(map);
            int startIndex = (curPage - 1) * pageSize;
            paramMap.put("startIndex", startIndex);
            List<String> fields = getFields(map);
            paramMap.put("fields", fields);
            return paramMap;
        }
        formatDate(resultList);
        Map resultMap = new HashMap();
        Map pagerMap = new HashMap();
        pagerMap.put("totalCount", count);
        pagerMap.put("pageSize", pageSize);
        pagerMap.put("curPage", curPage);
        resultMap.put("pagerVO", pagerMap);
        resultMap.put("result", resultList);
        return resultMap;
    }


    @Override
    public XSSFWorkbook exportcollectionMarketingListExcel(Map map) throws Exception {
        Map paramMap = formatTime(map);
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        sf.setTimeZone(TimeZone.getTimeZone("Asia/Shanghai"));
        SimpleDateFormat resData = new SimpleDateFormat("yyyy-MM-dd");
        List<String> fields = getFields(paramMap);
        paramMap.put("fields", fields);
        List<CollectionMarketingListModel> resultList = oneClickResponseDao.collectionMarketingList(paramMap);
        // 第一步：定义一个新的工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        // 第二步：创建一个Sheet页
        XSSFSheet sheet = wb.createSheet("collectionMarketingList");
        sheet.setDefaultRowHeight((short) (2 * 256));//设置行高
        sheet.setColumnWidth(0, 6000);//设置列宽
        sheet.setColumnWidth(1, 6000);
        sheet.setColumnWidth(2, 15000);
        sheet.setColumnWidth(3, 6000);
        sheet.setColumnWidth(4, 6000);
        sheet.setColumnWidth(5, 6000);
        sheet.setColumnWidth(6, 6000);
        sheet.setColumnWidth(7, 5000);
        sheet.setColumnWidth(8, 3000);
        sheet.setColumnWidth(9, 5000);
        sheet.setColumnWidth(10, 3000);
        sheet.setColumnWidth(11, 5000);
        sheet.setColumnWidth(12, 4000);
        sheet.setColumnWidth(13, 4000);
        XSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);

        //创建查询的添加的行
        XSSFCellStyle cellStyle1 = wb.createCellStyle();
        cellStyle1.setAlignment(HorizontalAlignment.CENTER);
        String[] taskTitle0 = {"开始时间:", "", "结束时间:", "", "市:", "", "区县:", "", "营业部:", "", "是否处理:", "", "揽收问题上报人:", ""};

        XSSFRow row = sheet.createRow(0);
        setCollectionAndMarketingTitle(map, sf, resData, resultList, taskTitle0);
        XSSFCell cell = null;
        for (int i = 0; i < taskTitle0.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(taskTitle0[i]);
            cell.setCellStyle(cellStyle1);
        }
        //写入导出行
        CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 1, 0, 13);
        sheet.addMergedRegion(cellRangeAddress);
        XSSFRow row1 = sheet.createRow(1);
        cell = row1.createCell(0);
        cell.setCellValue("导出");
        XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        cell.setCellStyle(cellStyle);
        row1.setHeight((short) 425);

        CellRangeAddress cellRangeAddress2_8_13 = new CellRangeAddress(2, 2, 8, 13);
        sheet.addMergedRegion(cellRangeAddress2_8_13);
        XSSFRow row2 = sheet.createRow(2);
        String[] taskTitles2 = {"市", "区县", "营业部", "揽收问题上报人", "邮件号", "是否处理状态", "揽收问题创建时间", "揽收问题结束时间", "问题描述"};
        if (map.get("processDefineName").equals("投递质量")) {
            taskTitles2[3] = "投递问题上报人";
            taskTitles2[6] = "投递问题创建时间";
            taskTitles2[7] = "投递问题结束时间";
        }
        for (int i = 0; i < taskTitles2.length; i++) {
            cell = row2.createCell(i);
            cell.setCellValue(taskTitles2[i]);
            cell.setCellStyle(cellStyle1);
        }
        //如果集合没有数据直接返回
        if (resultList.size() < 1) {
            return wb;
        }
        formatDate(resultList);
        setCollectionAndMarketingExcel(sf, resultList, sheet, cellStyle1);
        return wb;
    }

    /**
     * 揽收营销  明细标题设置
     *
     * @param map
     * @param sf
     * @param resData
     * @param resultList
     * @param taskTitle0
     * @throws ParseException
     */
    private void setCollectionAndMarketingTitle(Map map, SimpleDateFormat sf, SimpleDateFormat resData, List<CollectionMarketingListModel> resultList, String[] taskTitle0) throws ParseException {
        if (StringUtils.isNotBlank((String) map.get("startTime"))) {
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(sf.parse(map.get("startTime").toString()));
            calendar.add(Calendar.HOUR_OF_DAY, +8);
            taskTitle0[1] = resData.format(calendar.getTime());
        }
        if (StringUtils.isNotBlank((String) map.get("endTime"))) {
            taskTitle0[3] = resData.format(sf.parse(map.get("endTime").toString()));
        }
        if (StringUtils.isNotBlank((String) map.get("isHandle"))) {
            if ("Y".equals((String) map.get("isHandle"))) {
                taskTitle0[11] = "是";
            }
            if ("N".equals((String) map.get("isHandle"))) {
                taskTitle0[11] = "否";
            }
        }
        if (StringUtils.isNotBlank((String) map.get("empName"))) {
            taskTitle0[13] = map.get("empName").toString();
        }
        if (map.get("processDefineName").equals("投递质量")) {
            taskTitle0[12] = "投递问题上报人:";
        }
        setCityOrEmpName(null, resultList, taskTitle0, map);
    }

    /**
     * 设置揽收营销的表格的值
     *
     * @param sf
     * @param resultList
     * @param sheet
     * @param cellStyle1
     */
    private void setCollectionAndMarketingExcel(SimpleDateFormat sf, List<CollectionMarketingListModel> resultList, XSSFSheet sheet, XSSFCellStyle cellStyle1) {
        XSSFRow rows;
        XSSFCell cells;
        for (int i = 0; i < resultList.size(); i++) {
            rows = sheet.createRow(i + 3);
            CellRangeAddress cellRangeAddress8_13 = new CellRangeAddress(i + 3, i + 3, 8, 13);
            sheet.addMergedRegion(cellRangeAddress8_13);
            cells = rows.createCell(0);
            cells.setCellStyle(cellStyle1);
            cells.setCellValue(resultList.get(i).getCity_name());
            cells = rows.createCell(1);
            cells.setCellStyle(cellStyle1);
            cells.setCellValue(resultList.get(i).getCounty_name());
            cells = rows.createCell(2);
            cells.setCellStyle(cellStyle1);
            cells.setCellValue(resultList.get(i).getEmp_dept_name());
            cells = rows.createCell(3);
            cells.setCellStyle(cellStyle1);
            cells.setCellValue(resultList.get(i).getEmp_name());
            cells = rows.createCell(4);
            cells.setCellStyle(cellStyle1);
            cells.setCellValue(resultList.get(i).getMailNo());
            String status = resultList.get(i).getStatus();
            cells = rows.createCell(5);
            cells.setCellStyle(cellStyle1);
            if (status.equals("Completed")) {
                cells.setCellValue("是");
            } else {
                cells.setCellValue("否");
            }
            cells = rows.createCell(6);
            cells.setCellValue(sf.format(resultList.get(i).getCreated_date()));
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(7);
            if (status.equals("Completed")) {
                cells.setCellValue(sf.format(resultList.get(i).getLast_updated_date()));
            } else {
                cells.setCellValue("");
            }
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(8);
            cells.setCellValue(resultList.get(i).getQuestionMeno());
            cells.setCellStyle(cellStyle1);
        }
    }

    @Override
    public XSSFWorkbook exportdirectMailSearchListExcel(Map map) throws Exception {
        Map paramMap = formatTime(map);
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        sf.setTimeZone(TimeZone.getTimeZone("Asia/Shanghai"));
        SimpleDateFormat resData = new SimpleDateFormat("yyyy-MM-dd");
        List<String> fields = getFields(paramMap);
        paramMap.put("fields", fields);
        List<CollectionMarketingListModel> resultList = oneClickResponseDao.collectionMarketingList(paramMap);
        formatDate(resultList);

        // 第一步：定义一个新的工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        // 第二步：创建一个Sheet页
        XSSFSheet sheet = wb.createSheet("directMailSearchList");
        sheet.setDefaultRowHeight((short) (2 * 256));//设置行高
        sheet.setColumnWidth(0, 6000);//设置列宽
        sheet.setColumnWidth(1, 6000);
        sheet.setColumnWidth(2, 10000);
        sheet.setColumnWidth(3, 6000);
        sheet.setColumnWidth(4, 6000);
        sheet.setColumnWidth(5, 6000);
        sheet.setColumnWidth(6, 5000);
        sheet.setColumnWidth(7, 4000);
        sheet.setColumnWidth(8, 5500);
        sheet.setColumnWidth(9, 5000);
        sheet.setColumnWidth(10, 3000);
        sheet.setColumnWidth(11, 5000);
        sheet.setColumnWidth(12, 4000);
        sheet.setColumnWidth(13, 4000);
        XSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);

        //表格的查询条件   邮件直邮
        setDirectMailSearchCondition(map, sf, resData, resultList, wb, sheet);
        XSSFCell cell;
        //写入导出行
        CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 1, 0, 13);
        sheet.addMergedRegion(cellRangeAddress);
        XSSFRow row1 = sheet.createRow(1);
        cell = row1.createCell(0);
        cell.setCellValue("导出");
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        cell.setCellStyle(cellStyle);
        row1.setHeight((short) 425);
        XSSFRow row2 = sheet.createRow(2);
        CellRangeAddress cellRangeAddress2_8_13 = new CellRangeAddress(2, 2, 8, 13);
        sheet.addMergedRegion(cellRangeAddress2_8_13);
        String[] taskTitles = {
                "市", "区县", "营业部", "邮件直查上报人", "邮件号", "是否处理状态", "邮件直查创建时间", "邮件直查结束时间", "问题描述"
        };
        for (int i = 0; i < taskTitles.length; i++) {
            cell = row2.createCell(i);
            cell.setCellValue(taskTitles[i]);
        }
        //写入导出数据的
        setDirectMailData(sf, resultList, sheet);
        return wb;
    }


    /**
     * 写入导出的具体的数据到表格中
     *
     * @param sf
     * @param resultList
     * @param sheet
     */
    private void setDirectMailData(SimpleDateFormat sf, List<CollectionMarketingListModel> resultList, XSSFSheet sheet) {
        XSSFRow rows;
        XSSFCell cells;
        for (int i = 0; i < resultList.size(); i++) {
            rows = sheet.createRow(i + 3);
            CellRangeAddress cellRangeAddress8_13 = new CellRangeAddress(i + 3, i + 3, 8, 13);
            sheet.addMergedRegion(cellRangeAddress8_13);
            cells = rows.createCell(0);
            cells.setCellValue(resultList.get(i).getCity_name());
            cells = rows.createCell(1);
            cells.setCellValue(resultList.get(i).getCounty_name());
            cells = rows.createCell(2);
            cells.setCellValue(resultList.get(i).getEmp_dept_name());
            cells = rows.createCell(3);
            cells.setCellValue(resultList.get(i).getEmp_name());
            cells = rows.createCell(4);
            cells.setCellValue(resultList.get(i).getMailNo());
            String status = resultList.get(i).getStatus();
            cells = rows.createCell(5);
            if (status.equals("Completed")) {
                cells.setCellValue("是");
            } else {
                cells.setCellValue("否");
            }
            cells = rows.createCell(6);
            cells.setCellValue(sf.format(resultList.get(i).getCreated_date()));
            cells = rows.createCell(7);
            if (status.equals("Completed")) {
                cells.setCellValue(sf.format(resultList.get(i).getLast_updated_date()));
            } else {
                cells.setCellValue("");
            }
            cells = rows.createCell(8);
            cells.setCellValue(resultList.get(i).getQuestionMeno());
        }
    }


    /**
     * 设置邮件直查的导出表格的查询条件行
     *
     * @param map
     * @param sf
     * @param resData
     * @param resultList
     * @param wb
     * @param sheet
     * @throws ParseException
     */
    private void setDirectMailSearchCondition(Map map, SimpleDateFormat sf, SimpleDateFormat resData, List<CollectionMarketingListModel> resultList, XSSFWorkbook wb, XSSFSheet sheet) throws ParseException {
        XSSFCellStyle cellStyle1 = wb.createCellStyle();
        cellStyle1.setAlignment(HorizontalAlignment.CENTER);
        String[] taskTitle0 = {"开始时间:", "", "结束时间:", "", "市:", "", "区县:", "", "营业部:", "", "是否处理:", "", "邮件直查上报人:", ""};
        XSSFRow row = sheet.createRow(0);
        if (StringUtils.isNotBlank((String) map.get("startTime"))) {
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(sf.parse(map.get("startTime").toString()));
            calendar.add(Calendar.HOUR_OF_DAY, +8);
            taskTitle0[1] = resData.format(calendar.getTime());
        }
        if (StringUtils.isNotBlank((String) map.get("endTime"))) {
            taskTitle0[3] = resData.format(sf.parse(map.get("endTime").toString()));
        }
        if (StringUtils.isNotBlank((String) map.get("empName"))) {
            taskTitle0[13] = map.get("empName").toString();
        }
        if (StringUtils.isNotBlank((String) map.get("isHandle"))) {
            if ("Y".equals((String) map.get("isHandle"))) {
                taskTitle0[11] = "是";
            }
            if ("N".equals((String) map.get("isHandle"))) {
                taskTitle0[11] = "否";
            }
        }
        //从返回的参数中获取市或区县的值
        if (StringUtils.isNotBlank((String) map.get("cityCode"))) {
            taskTitle0[5] = resultList.get(0).getCity_name();
        }
        if (StringUtils.isNotBlank((String) map.get("countyCode"))) {
            taskTitle0[7] = resultList.get(0).getCounty_name();
        }
        if (StringUtils.isNotBlank((String) map.get("empDeptCode"))) {
            taskTitle0[9] = resultList.get(0).getEmp_dept_name();
        }
        XSSFCell cell = null;
        for (int i = 0; i < taskTitle0.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(taskTitle0[i]);
            cell.setCellStyle(cellStyle1);
        }
    }

    @Override
    public List<Map> getDepartmentListByCode(Map map) {
        logger.info("--区域维参数---：" + map);
        return oneClickResponseDao.getDepartmentListByCode(map);
    }

    @Override
    public XSSFWorkbook collectionMarketingForCityListExcel(Map map) throws ParseException {
        Map paramMap = formatTime(map);

        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat resData = new SimpleDateFormat("yyyy-MM-dd");
        List<String> fields = getFields(paramMap);
        paramMap.put("fields", fields);
        List<Map> resultList = this.collectionMarketingForCounty(paramMap);

        // 第一步：定义一个新的工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        // 第二步：创建一个Sheet页
        XSSFSheet sheet = wb.createSheet("directMailSearchList");
        sheet.setDefaultRowHeight((short) (2 * 256));//设置行高
        sheet.setColumnWidth(0, 6000);//设置列宽
        sheet.setColumnWidth(1, 6000);
        sheet.setColumnWidth(2, 6000);
        sheet.setColumnWidth(3, 6000);
        sheet.setColumnWidth(4, 8000);
        sheet.setColumnWidth(5, 8000);
        XSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);

        //创建查询的添加的行
        XSSFCellStyle cellStyle1 = wb.createCellStyle();
        cellStyle1.setAlignment(HorizontalAlignment.CENTER);
        String[] taskTitle0 = {"开始时间:", "", "结束时间:", "", "市:", ""};
        setMarketingSearchCondition(taskTitle0, map, sf, resData, resultList, cellStyle1, sheet);
        XSSFCell cell;
        //写入导出行
        CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 1, 0, 5);
        sheet.addMergedRegion(cellRangeAddress);
        XSSFRow row1 = sheet.createRow(1);
        cell = row1.createCell(0);
        cell.setCellValue("导出");
        XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        cell.setCellStyle(cellStyle);
        row1.setHeight((short) 425);
        //写入菜单栏
        XSSFRow row2 = sheet.createRow(2);
        //写入市级数据的导出标题
        setMarketingTitleForCity(map, cellStyle1, row2);
        //写入数据
        setMarketingDataForCity(resultList, sheet, cellStyle1);
        return wb;
    }

    /**
     * 写入营销数据
     *
     * @param resultList
     * @param sheet
     * @param cellStyle1
     */
    private void setMarketingDataForCity(List<Map> resultList, XSSFSheet sheet, XSSFCellStyle cellStyle1) {
        XSSFRow rows;
        XSSFCell cells;
        for (int i = 0; i < resultList.size(); i++) {
            rows = sheet.createRow(i + 3);
            cells = rows.createCell(0);
            cells.setCellValue(resultList.get(i).get("city_name").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(1);
            cells.setCellValue(resultList.get(i).get("total").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(2);
            cells.setCellValue(resultList.get(i).get("processedTotal").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(3);
            cells.setCellValue(resultList.get(i).get("untreatedTotal").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(4);
            cells.setCellValue(resultList.get(i).get("handleRate").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(5);
            cells.setCellValue(resultList.get(i).get("completedRate").toString());
            cells.setCellStyle(cellStyle1);
        }
    }

    /**
     * 市级营销数据标题导出
     *
     * @param map
     * @param cellStyle1
     * @param row2
     */
    private void setMarketingTitleForCity(Map map, XSSFCellStyle cellStyle1, XSSFRow row2) {
        XSSFCell cell;
        if ("邮件直查".equals((String) map.get("processDefineName"))) {
            String[] taskTitles = {"市", "邮件直查总数", "邮件直查已处理数", "邮件直查未处理数", "邮件直查及时（24小时）响应率", "邮件直查及时（24小时）关闭率"};
            for (int i = 0; i < taskTitles.length; i++) {
                cell = row2.createCell(i);
                cell.setCellValue(taskTitles[i]);
                cell.setCellStyle(cellStyle1);
            }
        }
        if ("投递质量".equals((String) map.get("processDefineName"))) {
            String[] taskTitles = {"市", "投递问题总数", "投递问题已处理数", "投递问题未处理数", "投递问题及时（24小时）响应率", "投递问题及时（24小时）关闭率"};
            for (int i = 0; i < taskTitles.length; i++) {
                cell = row2.createCell(i);
                cell.setCellValue(taskTitles[i]);
                cell.setCellStyle(cellStyle1);
            }
        }
        if ("揽收营销".equals((String) map.get("processDefineName"))) {
            String[] taskTitles = {"市", "揽收问题总数", "揽收问题已处理数", "揽收问题未处理数", "揽收问题及时（24小时）响应率", "揽收问题及时（24小时）关闭率"};
            for (int i = 0; i < taskTitles.length; i++) {
                cell = row2.createCell(i);
                cell.setCellValue(taskTitles[i]);
                cell.setCellStyle(cellStyle1);
            }
        }
    }

    /**
     * 营销数据导出搜索条件
     *
     * @param map
     * @param sf
     * @param resData
     * @param resultList
     * @param sheet
     * @param cellStyle1
     * @throws ParseException
     */
    private void setMarketingSearchCondition(String[] taskTitle0, Map map, SimpleDateFormat sf, SimpleDateFormat resData, List<Map> resultList, XSSFCellStyle cellStyle1, XSSFSheet sheet) throws ParseException {
        XSSFRow row = sheet.createRow(0);
        if (StringUtils.isNotBlank((String) map.get("startTime"))) {
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(sf.parse(map.get("startTime").toString()));
            calendar.add(Calendar.HOUR_OF_DAY, +8);
            taskTitle0[1] = resData.format(sf.parse(map.get("startTime").toString()));
        }
        if (StringUtils.isNotBlank((String) map.get("endTime"))) {
            taskTitle0[3] = resData.format(sf.parse(map.get("endTime").toString()));
        }

        setCityOrEmpName(resultList, null, taskTitle0, map);
        XSSFCell cell = null;
        for (int i = 0; i < taskTitle0.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(taskTitle0[i]);
            cell.setCellStyle(cellStyle1);
        }
    }

    @Override
    public XSSFWorkbook collectionMarketingForCountyListExcel(Map map) throws ParseException {
        Map paramMap = formatTime(map);
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat resData = new SimpleDateFormat("yyyy-MM-dd");
        List<String> fields = getFields(paramMap);
        paramMap.put("fields", fields);
        List<Map> resultList = this.collectionMarketingForCounty(paramMap);

        // 第一步：定义一个新的工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        // 第二步：创建一个Sheet页
        XSSFSheet sheet = wb.createSheet("directMailSearchList");
        sheet.setDefaultRowHeight((short) (2 * 256));//设置行高
        sheet.setColumnWidth(0, 6000);//设置列宽
        sheet.setColumnWidth(1, 6000);
        sheet.setColumnWidth(2, 6000);
        sheet.setColumnWidth(3, 6000);
        sheet.setColumnWidth(4, 6000);
        sheet.setColumnWidth(5, 8000);
        sheet.setColumnWidth(6, 6000);
        sheet.setColumnWidth(7, 6000);
        XSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);

        //创建查询的添加的行
        XSSFCellStyle cellStyle1 = (XSSFCellStyle) wb.createCellStyle();
        cellStyle1.setAlignment(HorizontalAlignment.CENTER);
        String[] taskTitle0 = {"开始时间:", "", "结束时间:", "", "市:", "", "区:", ""};
        setMarketingSearchCondition(taskTitle0, map, sf, resData, resultList, cellStyle1, sheet);
        XSSFCell cell;
        //写入导出行
        CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 1, 0, 7);
        sheet.addMergedRegion(cellRangeAddress);
        XSSFRow row1 = sheet.createRow(1);
        cell = row1.createCell(0);
        cell.setCellValue("导出");
        XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        cell.setCellStyle(cellStyle);
        row1.setHeight((short) 425);
        //写入菜单栏
        XSSFRow row2 = sheet.createRow(2);
        CellRangeAddress cellRangeAddress2_7_8 = new CellRangeAddress(2, 2, 6, 7);
        sheet.addMergedRegion(cellRangeAddress2_7_8);
        setMarketingTitleForCounty(map, cellStyle1, row2);
        setMarketingDataForCounty(resultList, sheet, cellStyle1);
        return wb;
    }

    /**
     * 设置营销导出数据的区县维度的数据
     *
     * @param resultList
     * @param sheet
     * @param cellStyle1
     */
    private void setMarketingDataForCounty(List<Map> resultList, XSSFSheet sheet, XSSFCellStyle cellStyle1) {
        XSSFRow rows;
        XSSFCell cells;
        for (int i = 0; i < resultList.size(); i++) {
            rows = sheet.createRow(i + 3);
            CellRangeAddress cellRangeAddress6_7 = new CellRangeAddress(i + 3, i + 3, 6, 7);
            sheet.addMergedRegion(cellRangeAddress6_7);
            cells = rows.createCell(0);
            cells.setCellValue(resultList.get(i).get("city_name").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(1);
            cells.setCellValue(resultList.get(i).get("county_name").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(2);
            cells.setCellValue(resultList.get(i).get("total").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(3);
            cells.setCellValue(resultList.get(i).get("processedTotal").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(4);
            cells.setCellValue(resultList.get(i).get("untreatedTotal").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(5);
            cells.setCellValue(resultList.get(i).get("handleRate").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(6);
            cells.setCellValue(resultList.get(i).get("completedRate").toString());
            cells.setCellStyle(cellStyle1);
        }
    }

    /**
     * 设置营销导出数据的区县维度菜单栏
     *
     * @param map
     * @param cellStyle1
     * @param row2
     */
    private void setMarketingTitleForCounty(Map map, XSSFCellStyle cellStyle1, XSSFRow row2) {
        XSSFCell cell;
        if ("邮件直查".equals((String) map.get("processDefineName"))) {
            String[] taskTitles = {"市", "区县", "邮件直查总数", "邮件直查已处理数", "邮件直查未处理数", "邮件直查及时（24小时）响应率", "邮件直查及时（24小时）关闭率"};
            for (int i = 0; i < taskTitles.length; i++) {
                cell = row2.createCell(i);
                cell.setCellValue(taskTitles[i]);
                cell.setCellStyle(cellStyle1);
            }
        }
        if ("投递质量".equals((String) map.get("processDefineName"))) {
            String[] taskTitles = {"市", "区县", "投递问题总数", "投递问题已处理数", "投递问题未处理数", "投递问题及时（24小时）响应率", "投递问题及时（24小时）关闭率"};
            for (int i = 0; i < taskTitles.length; i++) {
                cell = row2.createCell(i);
                cell.setCellValue(taskTitles[i]);
                cell.setCellStyle(cellStyle1);
            }
        }
        if ("揽收营销".equals((String) map.get("processDefineName"))) {
            String[] taskTitles = {"市", "区县", "揽收问题总数", "揽收问题已处理数", "揽收问题未处理数", "揽收问题及时（24小时）响应率", "揽收问题及时（24小时）关闭率"};
            for (int i = 0; i < taskTitles.length; i++) {
                cell = row2.createCell(i);
                cell.setCellValue(taskTitles[i]);
                cell.setCellStyle(cellStyle1);
            }
        }
    }

    @Override
    public XSSFWorkbook collectionMarketingForDepartmentListExcel(Map map) throws ParseException {
        Map paramMap = formatTime(map);
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat resData = new SimpleDateFormat("yyyy-MM-dd");
        List<String> fields = getFields(paramMap);
        paramMap.put("fields", fields);
        List<Map> resultList = this.collectionMarketingForDepartment(paramMap);
        // 第一步：定义一个新的工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        // 第二步：创建一个Sheet页
        XSSFSheet sheet = wb.createSheet("directMailSearchList");
        sheet.setDefaultRowHeight((short) (2 * 256));//设置行高
        sheet.setColumnWidth(0, 6000);//设置列宽
        sheet.setColumnWidth(1, 6000);
        sheet.setColumnWidth(2, 6000);
        sheet.setColumnWidth(3, 6000);
        sheet.setColumnWidth(4, 6000);
        sheet.setColumnWidth(5, 6000);
        sheet.setColumnWidth(6, 8000);
        sheet.setColumnWidth(7, 8000);
        sheet.setColumnWidth(8, 6000);
        sheet.setColumnWidth(9, 10000);
        XSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);

        //创建查询的添加的行
        XSSFCellStyle cellStyle1 = wb.createCellStyle();
        cellStyle1.setAlignment(HorizontalAlignment.CENTER);
        XSSFRow row = sheet.createRow(0);
        String[] taskTitle0 = {"开始时间:", "", "结束时间:", "", "市:", "", "区:", "", "营业部", ""};
        setMarketingSearchCondition(taskTitle0, map, sf, resData, resultList, cellStyle1, sheet);
        XSSFCell cell = null;
        for (int i = 0; i < taskTitle0.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(taskTitle0[i]);
            cell.setCellStyle(cellStyle1);
        }
        //写入导出行
        CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 1, 0, 9);
        sheet.addMergedRegion(cellRangeAddress);
        XSSFRow row1 = sheet.createRow(1);
        cell = row1.createCell(0);
        cell.setCellValue("导出");
        XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        cell.setCellStyle(cellStyle);
        row1.setHeight((short) 425);
        //写入菜单栏
        XSSFRow row2 = sheet.createRow(2);
        CellRangeAddress cellRangeAddress2_7_8 = new CellRangeAddress(2, 2, 7, 9);
        sheet.addMergedRegion(cellRangeAddress2_7_8);
        setMarketingTitleForDepartment(map, cellStyle1, row2);
        setMarketingDataForDepartment(resultList, sheet, cellStyle1);
        return wb;
    }

    /**
     * 设置营销导出数据的营业部维度的数据
     *
     * @param resultList
     * @param sheet
     * @param cellStyle1
     */
    private void setMarketingDataForDepartment(List<Map> resultList, XSSFSheet sheet, XSSFCellStyle cellStyle1) {
        XSSFRow rows;
        XSSFCell cells;
        for (int i = 0; i < resultList.size(); i++) {
            rows = sheet.createRow(i + 3);
            CellRangeAddress cellRangeAddress7_9 = new CellRangeAddress(i + 3, i + 3, 7, 9);
            sheet.addMergedRegion(cellRangeAddress7_9);
            cells = rows.createCell(0);
            cells.setCellValue(resultList.get(i).get("city_name").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(1);
            cells.setCellValue(resultList.get(i).get("county_name").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(2);
            cells.setCellValue(resultList.get(i).get("dept_name").toString());
            cells.setCellStyle(cellStyle1);
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(3);
            cells.setCellValue(resultList.get(i).get("total").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(4);
            cells.setCellValue(resultList.get(i).get("processedTotal").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(5);
            cells.setCellValue(resultList.get(i).get("untreatedTotal").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(6);
            cells.setCellValue(resultList.get(i).get("handleRate").toString());
            cells.setCellStyle(cellStyle1);
            cells = rows.createCell(7);
            cells.setCellValue(resultList.get(i).get("completedRate").toString());
            cells.setCellStyle(cellStyle1);
        }
    }

    /**
     * 设置营销导出数据的营业部维度菜单栏
     *
     * @param map
     * @param cellStyle1
     * @param row2
     */
    private void setMarketingTitleForDepartment(Map map, XSSFCellStyle cellStyle1, XSSFRow row2) {
        XSSFCell cell;
        if ("邮件直查".equals((String) map.get("processDefineName"))) {
            String[] taskTitles = {"市", "区县", "营业部", "邮件直查总数", "邮件直查已处理数", "邮件直查未处理数", "邮件直查及时（24小时）响应率", "邮件直查及时（24小时）关闭率"};
            for (int i = 0; i < taskTitles.length; i++) {
                cell = row2.createCell(i);
                cell.setCellValue(taskTitles[i]);
                cell.setCellStyle(cellStyle1);
            }
        }
        if ("投递质量".equals((String) map.get("processDefineName"))) {
            String[] taskTitles = {"市", "区县", "营业部", "投递质量总数", "投递质量已处理数", "投递质量未处理数", "投递质量及时（24小时）响应率", "投递质量及时（24小时）关闭率"};
            for (int i = 0; i < taskTitles.length; i++) {
                cell = row2.createCell(i);
                cell.setCellValue(taskTitles[i]);
                cell.setCellStyle(cellStyle1);
            }
        }
        if ("揽收营销".equals((String) map.get("processDefineName"))) {
            String[] taskTitles = {"市", "区县", "营业部", "揽收营销总数", "揽收营销已处理数", "揽收营销未处理数", "揽收营销及时（24小时）响应率", "揽收营销及时（24小时）关闭率"};
            for (int i = 0; i < taskTitles.length; i++) {
                cell = row2.createCell(i);
                cell.setCellValue(taskTitles[i]);
                cell.setCellStyle(cellStyle1);
            }
        }
    }


    /**
     * 设置省市营业部的名字
     *
     * @param resultList
     * @param taskTitle0
     */
    private void setCityOrEmpName(List<Map> resultList, List<CollectionMarketingListModel> CollectionMarketList, String[] taskTitle0, Map map) {
        String cityCode = (String) map.get("cityCode");
        String countyCode = (String) map.get("countyCode");
        String empDeptCode = (String) map.get("empDeptCode");
        if (!CollectionUtils.isEmpty(resultList)) {
            //从返回的参数中获取市或区县的值
            if (StringUtils.isNotBlank(cityCode)) {
                taskTitle0[5] = resultList.get(0).get("city_name").toString();
            }
            if (StringUtils.isNotBlank(countyCode)) {

                taskTitle0[7] = resultList.get(0).get("county_name").toString();
            }
            if (StringUtils.isNotBlank(empDeptCode)) {
                taskTitle0[9] = resultList.get(0).get("dept_name").toString();
            }
        } else if (!CollectionUtils.isEmpty(CollectionMarketList)) {
            //从返回的参数中获取市或区县的值
            if (StringUtils.isNotBlank(cityCode)) {
                taskTitle0[5] = CollectionMarketList.get(0).getCity_name();
            }
            if (StringUtils.isNotBlank(countyCode)) {

                taskTitle0[7] = CollectionMarketList.get(0).getCounty_name();
            }
            if (StringUtils.isNotBlank(empDeptCode)) {
                taskTitle0[9] = CollectionMarketList.get(0).getEmp_dept_name();
            }

        } else {
            //没有返回参数从数据库查询
            if (StringUtils.isNotBlank(empDeptCode)) {
                Map<String, String> nameMap = getNameByCode(empDeptCode);
                taskTitle0[9] = nameMap.get("dept_name");
                taskTitle0[7] = nameMap.get("county_name");
                taskTitle0[5] = nameMap.get("city_name");
            } else if (StringUtils.isNotBlank(countyCode)) {
                Map<String, String> nameMap = getNameByCode(countyCode);
                taskTitle0[7] = nameMap.get("county_name");
                taskTitle0[5] = nameMap.get("city_name");
            } else {
                Map<String, String> nameMap = getNameByCode(cityCode);
                taskTitle0[5] = nameMap.get("city_name");
            }
        }
    }


    public Map formatTime(Map map) {
//创建一个入参的map集合
        Map<Object, Object> paramMap = new HashMap<>();
        paramMap.putAll(map);
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            if (null != map.get("startTime") && !"".equals(map.get("startTime"))) {
                String startTime = (String) map.get("startTime");
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(sdf.parse(startTime));
                calendar.add(Calendar.HOUR_OF_DAY, -8);
                paramMap.put("startTime", sdf.format(calendar.getTime()));
            }

            if (null != map.get("endTime") && !"".equals(map.get("endTime"))) {
                String endTime = (String) map.get("endTime");
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(sdf.parse(endTime));
                calendar.add(Calendar.HOUR_OF_DAY, -8);
                paramMap.put("endTime", sdf.format(calendar.getTime()));
            }

        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return paramMap;
    }

    public void formatDate(List<CollectionMarketingListModel> list) {

        try {
            if (list.size() > 0) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                for (CollectionMarketingListModel collectionMarketingListModel : list) {
                    if (null != collectionMarketingListModel.getCreated_date()) {
                        String startTime = sdf.format(collectionMarketingListModel.getCreated_date());
                        Calendar calendar = Calendar.getInstance();
                        calendar.setTime(sdf.parse(startTime));
                        calendar.add(Calendar.HOUR_OF_DAY, +8);
                        calendar.getTime();
                        collectionMarketingListModel.setCreated_date(calendar.getTime());
                    }

                    if (null != collectionMarketingListModel.getLast_updated_date()) {
                        String endTime = sdf.format(collectionMarketingListModel.getLast_updated_date());
                        Calendar calendar = Calendar.getInstance();
                        calendar.setTime(sdf.parse(endTime));
                        calendar.add(Calendar.HOUR_OF_DAY, +8);
                        collectionMarketingListModel.setLast_updated_date(calendar.getTime());
                    }
                }
            }
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 根据模块名获取模块名对应的常量
     *
     * @param map
     * @return
     */
    private List<String> getFields(Map map) {
        List<String> fields = new ArrayList<String>();
        List<ConstantDb> constantDbs = constantDbService.getConstantDbs();
        for (ConstantDb constantDb : constantDbs) {
        	if (constantDb.getType().equals(map.get("processDefineName"))) {
                fields.add(constantDb.getField());
            }
        }
        Assert.notEmpty(fields, "参数错误");
        return fields;
    }

    /**
     * 根据查询出来的参数，计算出对应的及时率的问题
     *
     * @param resultMap
     */
    private void setOnTimeRate(List<Map> resultMap) {
        if (resultMap.size() > 0) {
            for (Map reMap : resultMap) {
                BigDecimal total = (BigDecimal) reMap.get("total");
                if (null != reMap.get("handle24")) {
                    BigDecimal handleCount = (BigDecimal) reMap.get("handle24");
                    BigDecimal handleRate = handleCount.divide(total, 2, BigDecimal.ROUND_HALF_UP);
                    Double handleRate1 = handleRate.multiply(new BigDecimal(100)).doubleValue();
                    reMap.put("handleRate", handleRate1 + "%");
                } else {
                    reMap.put("handleRate", "0.00%");
                }

                if (null != reMap.get("completed24")) {
                    BigDecimal completedCount = (BigDecimal) reMap.get("completed24");
                    BigDecimal completedRate = completedCount.divide(total, 2, BigDecimal.ROUND_HALF_UP);
                    Double completedRate1 = completedRate.multiply(new BigDecimal(100)).doubleValue();
                    reMap.put("completedRate", completedRate1 + "%");
                } else {
                    reMap.put("completedRate", "0.00%");
                }
            }
        }
    }

    /**
     * 根据code查询营业部名字和市名字
     *
     * @param code
     * @return
     */
    public Map getNameByCode(String code) {
        Map nameMap = this.oneClickResponseDao.getEmpNameByCode(code);
        Assert.notEmpty(nameMap, "参数错误！没有查询到信息！");
        return nameMap;
    }

}
